﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace VaccineEbook.Models.DBEntity
{
    public partial class Chapter
    {
        #region Select
        internal static int? GetMaxPage(int _bookId)
        {
            VaccineEBookEntities context = null;
            try
            {
                context = new VaccineEBookEntities();
                var query = from tb in context.Chapters
                            where tb.BookID == _bookId
                            orderby tb.Page descending
                            select tb;
                return query.First().Page;
            }
            catch (Exception ex)
            {
                return 0;
            }
        }

        internal static int GetMaxChapterID(int _bookId)
        {
            VaccineEBookEntities context = null;
            try
            {
                context = new VaccineEBookEntities();
                var query = from tb in context.Chapters
                            where tb.BookID == _bookId
                            orderby tb.ChapterID descending
                            select tb;
                return query.First().ChapterID.GetValueOrDefault();
            }
            catch (Exception ex)
            {
                return 0;
            }
        }

        internal static IEnumerable<Chapter> QuickSearch(string _strSeach, int _bookID, ref string _messageError)
        {
            VaccineEBookEntities context = null;
            try
            {
                context = new VaccineEBookEntities(Constant.DEFAUT_TIMEOUT_SECOND);
                var query = from tb in context.Chapters
                            where tb.BookID == _bookID && (
                                string.IsNullOrEmpty(_strSeach) ||
                                tb.ChapterContent.Contains(_strSeach) ||
                                tb.ChappterGroup.Contains(_strSeach) ||
                                tb.ChapterDescription.Contains(_strSeach)
                            )
                            orderby tb.Page
                            select tb;
                return query;
            }
            catch (Exception ex)
            {
                _messageError = ex.Message;
                return null;
            }
        }

        internal static Chapter GetByID(int _id, ref string _messageError)
        {
            VaccineEBookEntities context = null;
            try
            {
                context = new VaccineEBookEntities();
                var query = from tb in context.Chapters
                            where tb.ID == _id
                            select tb;
                return query.First();
            }
            catch (Exception ex)
            {
                _messageError = ex.Message;
                return null;
            }
        }

        internal static IEnumerable<Chapter> GetByChapterID(int _bookID, int _chapterID, ref string _messageError)
        {
            VaccineEBookEntities context = null;
            try
            {
                context = new VaccineEBookEntities();
                var query = from tb in context.Chapters
                            where tb.BookID == _bookID && 
                            tb.ChapterID == _chapterID
                            orderby tb.Page
                            select tb;
                return query;
            }
            catch (Exception ex)
            {
                _messageError = ex.Message;
                return null;
            }
        }

        #endregion


        #region Add new
        internal static Chapter AddNew(Chapter _chapter, int _createBy, ref string _messageError)
        {
            VaccineEBookEntities context = null;
            try
            {
                context = new VaccineEBookEntities();

                // validate page
                int maxPage = GetMaxPage(1).GetValueOrDefault();
                if (_chapter.Page <= 0 || _chapter.Page > maxPage + 1)
                {
                    _messageError = "Page from 1 to " + (maxPage + 1);
                    return null;
                }

                _chapter.Version = "1.0.0";
                _chapter.LastModifined = DateTime.Now;
                _chapter.CreatedBy = _createBy;

                // compare current ChappterGroup width after, before page (add new width exist chapterid, or not)
                bool hadChangeChapterID = false;
                Chapter beforeChapter = new Chapter();
                Chapter afterChapter = new Chapter();

                var query = context.Chapters.Where(m => m.Page == _chapter.Page - 1 && m.BookID == _chapter.BookID);
                if (query.Count() >= 1)
                {
                    // nếu tồn tại chapter before
                    beforeChapter = query.First();
                    if (_chapter.ChappterGroup.ToUpper() == beforeChapter.ChappterGroup.ToUpper())
                    {
                        _chapter.ChapterID = beforeChapter.ChapterID;
                        hadChangeChapterID = true;
                    }
                    else
                    {
                        query = context.Chapters.Where(m => m.Page == _chapter.Page && m.BookID == _chapter.BookID);
                        if (query.Count() >= 1)
                        {
                            // nếu tồn tại chapter after
                            afterChapter = query.First();
                            if (_chapter.ChappterGroup.ToUpper() == afterChapter.ChappterGroup.ToUpper())
                            {
                                _chapter.ChapterID = afterChapter.ChapterID;
                                hadChangeChapterID = true;
                            }
                        }
                    }
                }
                // add new not exist chapterid
                if (!hadChangeChapterID)
                {
                    _chapter.ChapterID = beforeChapter.ChapterID.GetValueOrDefault() + 1; // nếu là đầu tiên vẫn là 1, trang cuối vẫn là trang trước + 1

                    // update all chapterID, if page after
                    var query2 = from tb in context.Chapters
                                 where tb.BookID == _chapter.BookID
                                 && tb.ChapterID > _chapter.ChapterID
                                 select tb;
                    // chèn giữa 1 chương, 1 chương mới => tăng trang lên 2
                    if (beforeChapter.ChappterGroup == afterChapter.ChappterGroup && !string.IsNullOrEmpty(beforeChapter.ChappterGroup))
                        foreach (var c in query2)
                            c.ChapterID = c.ChapterID.GetValueOrDefault() + 2;
                    else
                        foreach (var c in query2)
                            c.ChapterID = c.ChapterID.GetValueOrDefault() + 1;
                }


                // update all page after +1, (dấu = vì nó chiếm trang nhưng chưa add, trang sau nó vẫn giữ trang đó)
                var query3 = from tb in context.Chapters
                        where tb.BookID == _chapter.BookID
                        && tb.Page >= _chapter.Page
                        select tb;
                foreach (var c in query3)
                {
                    c.Page++;
                }

                context.Chapters.Add(_chapter);

                context.SaveChanges();
                return _chapter;
            }
            catch (Exception ex)
            {
                _messageError = ex.Message;
                return null;
            }
        }
        #endregion


        #region Update
        /// <summary>
        /// Update Chapter base chapter id
        /// Not update: chapter id, bookid, chapterid, chapter group, page, create by, version
        /// </summary>
        /// <param name="_chapterID"></param>
        /// <param name="_chapter"></param>
        /// <param name="_messageError"></param>
        /// <returns></returns>
        internal static Chapter Update(int _chapterID, Chapter _chapter, ref string _messageError)
        {
            VaccineEBookEntities context = null;
            try
            {
                context = new VaccineEBookEntities();
                var query = from tb in context.Chapters
                            where tb.ID == _chapterID
                            select tb;

                Chapter newChapter = query.First();
                newChapter.Icon = _chapter.Icon;
                newChapter.ChapterContent = _chapter.ChapterContent;
                newChapter.ChapterDescription = _chapter.ChapterDescription;
                newChapter.LastModifined = DateTime.Now;

                context.SaveChanges();
                return newChapter;
            }
            catch (Exception ex)
            {
                _messageError = ex.Message;
                return null;
            }
        }
        #endregion



        #region Delete
        internal static Chapter Delete(int _id, ref string _messageError)
        {
            VaccineEBookEntities context = null;
            try
            {
                context = new VaccineEBookEntities();
                var query = from tb in context.Chapters
                                  where tb.ID == _id
                                  select tb;
                Chapter chapter = query.First();

                // update all page(-1) after 
                var query2 = from tb in context.Chapters
                        where tb.BookID == chapter.BookID
                        && tb.Page > chapter.Page
                        select tb;
                foreach (var c in query2)
                {
                    c.Page--;
                }

                // update chapterid
                // if last item has chapterid => update chapterid(+1) after chapter 
                var query3 = from tb in context.Chapters
                        where tb.BookID == chapter.BookID
                        && tb.ID != chapter.ID
                        && tb.ChapterID == chapter.ChapterID
                        select tb;
                if (query3.Count() <= 0)
                {
                    var query4 = from tb in context.Chapters
                        where tb.BookID == chapter.BookID
                        && tb.ChapterID > chapter.ChapterID
                        select tb;
                    foreach (var c in query4)
                    {
                        c.ChapterID = c.ChapterID.GetValueOrDefault() + 1;
                    }
                }

                // Delete 
                context.Chapters.Remove(chapter);

                context.SaveChanges();
                return chapter;
            }
            catch (Exception ex)
            {
                _messageError = ex.Message;
                return null;
            }
            finally
            {
                context = null;
                GC.Collect();
            }
        }
        #endregion

        internal static bool DeleteByChapterGroup(int _bookID, int _chapterID, ref string _messageError)
        {
            VaccineEBookEntities context = null;
            try
            {
                context = new VaccineEBookEntities();
                var query = from tb in context.Chapters
                            where tb.ChapterID == _chapterID
                            select tb;

                
                // độ lệch trang
                Chapter firstChapter = query.OrderBy(m=>m.Page).First();
                Chapter lastChapter = query.OrderByDescending(m => m.Page).First();
                int pageGap = lastChapter.Page.GetValueOrDefault() - firstChapter.Page.GetValueOrDefault() + 1;

                // update all page(-pageGap) after 
                var query2 = from tb in context.Chapters
                            where tb.BookID == _bookID
                            & tb.Page > lastChapter.Page
                            select tb;
                foreach (var c in query2)
                {
                    c.Page -= pageGap;
                }

                // update chapterid
                // update chapterid(-1) after chapter 
                query2 = from tb in context.Chapters
                        where tb.BookID == _bookID
                        && tb.ChapterID >= _chapterID
                        select tb;
                foreach (var c in query2)
                {
                    c.ChapterID -= 1;
                }

                // delete all
                foreach (var c in query)
                {
                    context.Chapters.Remove(c);
                }

                context.SaveChanges();
                return true;
            }
            catch (Exception ex)
            {
                _messageError = ex.Message;
                return false;
            }
            finally
            {
                context = null;
                GC.Collect();
            }
        }
    }
}